import sqlite3
from Common import *

DB_NAME = 'data/ka.db'

class DB:
    def __init__(self, logger):
        self.connection = None
        self.cursor = None
        self.logger = logger

    def call(self, method, args):
        # 判斷方法是否存在
        if hasattr(self, method) == False:
            self.logger.warning('method not exist: ' + method)
            return makeReturn(Error.METHOD_NOT_EXIST)
        # 連接數據庫
        try:
            self.connection = sqlite3.connect(DB_NAME)
            self.cursor = self.connection.cursor()
        except:
            self.logger.warning('db connect failed.')
            return makeReturn(Error.DATABASE_ERROR)
        # 執行
        ret = getattr(self, method)(args)
        if ret['errno'] != Error.SUCCESS:
            return ret
        # 關閉數據庫連接
        self.connection.close()
        return ret

    def __executeTransaction(self, sqls):
        try:
            self.cursor.execute('BEGIN TRANSACTION')
            for sql in sqls:
                self.cursor.execute(sql)
            self.connection.commit()
        except Exception as err:
            self.connection.rollback()
            self.logger.warning(err)
            self.logger.warning('transaction execute failed. sqls:' + str(sqls))
            return makeReturn(Error.DATABASE_ERROR)
        return makeReturn(Error.SUCCESS)

    def __selectData(self, attrs, tableName, condition, returnRaw, orderBy=''):
        ''' 自动生成SELECT语句
        attrs: list，要读取的列的名字列表
        tableName: str，表名
        condition: str，条件表达式，不包含WHERE关键字
        returnRaw：bool，是否直接返回sqlite返回的数据
        orderBy: str，是否需要排序，空字符串表示不排序，否则按照orderBy的内容排序'''
        try:
            # 拼SQL语句
            if len(attrs) == 0:
                raise ValueError('attrs is empty!')
            sqlStr = 'SELECT '
            for attr in attrs:
                sqlStr += attr + ', '
            sqlStr = sqlStr[:-2] + ' FROM ' + tableName
            if condition != '':
                sqlStr += ' WHERE ' + condition
            if orderBy != '':
                sqlStr += ' ORDER BY ' + orderBy
            # 执行SQL语句，并获取结果
            connection = sqlite3.connect(DB_NAME)
            cursor = connection.cursor()
            cursor.execute(sqlStr)
            dbData = cursor.fetchall()
        except:
            self.logger.warning('select failed. sql: ' + sqlStr)
            return makeReturn(Error.DATABASE_ERROR)
        cursor.close()
        connection.close()
        if returnRaw == True:
            return makeReturn(Error.SUCCESS, dbData)
        # 把属性名字加入数据库返回的数据中
        ret = []
        for row in dbData:
            rowMap = {}
            for i in range(0, len(attrs)):
                rowMap[attrs[i]] = row[i]
            ret.append(rowMap)
        return makeReturn(Error.SUCCESS, ret)

    def getAllAccountInfo(self, args):
        show_delete = args['show_delete']
        returnRaw = args['return_raw']
        attrs = ['acnt_id', 'crc_id', 'nameme', 'typepe', 'showable', 'deleteable']
        condition = '' # 默认获取所有记录
        if show_delete == False:
            condition = 'showable = 1'
        orderBy = 'typepe ASC'
        return self.__selectData(attrs, 'account', condition, returnRaw, orderBy)

    def getAllAccountInfoOrganizedByCurrency(self, args):
        show_delete = args['show_delete']
        returnRaw = args['return_raw']
        attrs = ['acnt_id', 'crc_id', 'nameme', 'typepe', 'showable', 'deleteable']
        condition = '' # 默认获取所有记录
        if show_delete == False:
            condition = 'showable = 1'
        # 獲取賬戶信息
        orderBy = 'typepe ASC'
        accountInfo = self.__selectData(attrs, 'account', condition, returnRaw, orderBy)
        if accountInfo['errno'] != Error.SUCCESS:
            return accountInfo
        else :
            accountInfo = accountInfo['data']
        # 獲取幣種信息
        attrs = ['crc_id', 'nameme', 'unit', 'characterter', 'showable']
        currencyInfo = self.__selectData(attrs, 'currency', condition, returnRaw)
        if currencyInfo['errno'] != Error.SUCCESS:
            return currencyInfo
        else :
            currencyInfo = currencyInfo['data']
        # 把幣種和賬戶信息拼起來
        ret = {}
        for info in currencyInfo:
            crc_id = info['crc_id']
            info['account_list'] = []
            ret[crc_id] = info
        for info in accountInfo:
            crc_id = info['crc_id']
            if crc_id in ret:   # 过滤掉已删除的currency对应的account
                ret[crc_id]['account_list'].append(info)
        ret = list(ret.values())
        return makeReturn(Error.SUCCESS, ret)

    def addAccount(self, args):
        sqls = ['INSERT INTO account (acnt_id, crc_id, typepe, nameme, descriptiontion, showable, deleteable) VALUES (NULL, %s, %s, \'%s\', \'%s\', 1, 1)'%\
            (args['crc_id'], args['typepe'], args['nameme'], args['descriptiontion'])]
        return self.__executeTransaction(sqls)

    def editAccount(self, args):
        sqls = ['UPDATE account SET nameme = "%s" WHERE acnt_id = %s'%(args['nameme'], args['acnt_id'])]
        return self.__executeTransaction(sqls)

    def deleteAccount(self, args):
        sqls = ['UPDATE account SET showable = 0 WHERE acnt_id = ' + args['acnt_id']]
        return self.__executeTransaction(sqls)

    def getIncomeCategory(self, args):
        show_delete = args['show_delete']
        returnRaw = args['return_raw']
        attrs = ['cat1_id', 'nameme', 'showable']
        condition = ''
        if show_delete == False:
            condition = 'showable = 1'
        return self.__selectData(attrs, 'income_cat1', condition, returnRaw)

    def getPaymentCategory1(self, args):
        show_delete = args['show_delete']
        returnRaw = args['return_raw']
        attrs = ['cat1_id', 'nameme', 'showable']
        condition = 'cat1_id != 1'
        if show_delete == False:
            condition += ' and showable = 1'
        return self.__selectData(attrs, 'payment_cat1', condition, returnRaw)

    def getPaymentCategory2(self, args):
        show_delete = args['show_delete']
        returnRaw = args['return_raw']
        attrs = ['cat2_id', 'cat1_id', 'nameme', 'short_names', 'showable']
        condition = ''
        if show_delete == False:
            condition = 'showable = 1'
        return self.__selectData(attrs, 'payment_cat2', condition, returnRaw)
        
    def getTransferCategory(self, args):
        show_delete = args['show_delete']
        returnRaw = args['return_raw']
        attrs = ['cat1_id', 'nameme', 'showable']
        condition = ''
        if show_delete == False:
            condition = 'showable = 1'
        return self.__selectData(attrs, 'transfer_cat1', condition, returnRaw)

    def addIncomeCategory(self, args):
        sqls = ['INSERT INTO income_cat1 (cat1_id, nameme, showable) VALUES (NULL,"' + args['nameme'] + '",1)']
        return self.__executeTransaction(sqls)

    def addPaymentCategory1(self, args):
        sqls = ['INSERT INTO payment_cat1 (cat1_id, nameme, showable) VALUES (NULL,"' + args['nameme'] + '",1)']
        return self.__executeTransaction(sqls)

    def addPaymentCategory2(self, args):
        sqls = ['INSERT INTO payment_cat2 (cat2_id, cat1_id, nameme, short_names, showable) VALUES (NULL,"%s","%s","%s",1)'%(
            args['cat1_id'], args['nameme'], args['short_names'])]
        return self.__executeTransaction(sqls)

    def addTransferCategory(self, args):
        sqls = ['INSERT INTO transfer_cat1 (cat1_id, nameme, showable) VALUES (NULL,"' + args['nameme'] + '",1)']
        return self.__executeTransaction(sqls)

    def editIncomeCategory(self, args):
        sqls = ['UPDATE income_cat1 SET nameme = "%s" WHERE cat1_id = %s'%(args['nameme'], args['cat1_id'])]
        return self.__executeTransaction(sqls)

    def editPaymentCategory1(self, args):
        sqls = ['UPDATE payment_cat1 SET nameme = "%s" WHERE cat1_id = %s'%(args['nameme'], args['cat1_id'])]
        return self.__executeTransaction(sqls)
        
    def editPaymentCategory2(self, args):
        sqls = ['UPDATE payment_cat2 SET nameme = "%s", short_names = "%s" WHERE cat2_id = %s'%(args['nameme'], args['short_names'], args['cat2_id'])]
        return self.__executeTransaction(sqls)
        
    def editTransferCategory(self, args):
        sqls = ['UPDATE transfer_cat1 SET nameme = "%s" WHERE cat1_id = %s'%(args['nameme'], args['cat1_id'])]
        return self.__executeTransaction(sqls)

    def deleteIncomeCategory(self, args):
        sqls = ['UPDATE income_cat1 SET showable = 0 WHERE cat1_id = ' + args['cat1_id']]
        return self.__executeTransaction(sqls)

    def deletePaymentCategory1(self, args):
        sqls = ['UPDATE payment_cat1 SET showable = 0 WHERE cat1_id = ' + args['cat1_id'],
        'DELETE FROM budget_plan WHERE payment_cat1_id = ' + args['cat1_id']]
        return self.__executeTransaction(sqls)

    def deletePaymentCategory2(self, args):
        sqls = ['UPDATE payment_cat2 SET showable = 0 WHERE cat2_id = ' + args['cat2_id']]
        return self.__executeTransaction(sqls)

    def deleteTransferCategory(self, args):
        sqls = ['UPDATE transfer_cat1 SET showable = 0 WHERE cat1_id = ' + args['cat1_id']]
        return self.__executeTransaction(sqls)

    def addIncomeRecord(self, args):
        user_id = args['user_id']
        crc_id = args['currency_id']
        acnt_id = args['account_id']
        timeme = args['current_time']
        amount = args['amount']
        cat1_id = args['cat1_id']
        description = args['description']
        ret = timestampToCurrentDayMonthYearTimestamp(timeme)
        dayTime = ret['day']
        monthTime = ret['month']
        yearTime = ret['year']
        sqls = []
        sqls.append('INSERT INTO income_record(rcd_id, user_id, acnt_id, timeme, amount, cat1_id, describebe, showable) VALUES (NULL, %s, %s, %s, %s, %s, \'%s\', 1)'%(user_id, acnt_id, timeme, amount, cat1_id, description))
        sqls.append('UPDATE account_balance SET balance = balance + %s WHERE acnt_id = %s'%(amount, acnt_id))
        sqls.append('INSERT OR IGNORE INTO daily_stat_record(timeme, crc_id, income, payment) VALUES (%s, %s, 0, 0)'%(dayTime, crc_id))
        sqls.append('UPDATE daily_stat_record SET income = income + %s WHERE timeme = %s and crc_id = %s'%(amount, dayTime, crc_id))
        sqls.append('INSERT OR IGNORE INTO monthly_stat_record(timeme, crc_id, income, payment) VALUES (%s, %s, 0, 0)'%(monthTime, crc_id))
        sqls.append('UPDATE monthly_stat_record SET income = income + %s WHERE timeme = %s and crc_id = %s'%(amount, monthTime, crc_id))
        sqls.append('INSERT OR IGNORE INTO annually_stat_record(timeme, crc_id, income, payment) VALUES (%s, %s, 0, 0)'%(yearTime, crc_id))
        sqls.append('UPDATE annually_stat_record SET income = income + %s WHERE timeme = %s and crc_id = %s'%(amount, yearTime, crc_id))
        return self.__executeTransaction(sqls)

    def addPaymentRecord(self, args):
        user_id = args['user_id']
        crc_id = args['currency_id']
        acnt_id = args['account_id']
        timeme = args['current_time']
        amount = args['amount']
        cat1_id = args['cat1_id']
        cat2_id = args['cat2_id']
        description = args['description']
        ret = timestampToCurrentDayMonthYearTimestamp(timeme)
        dayTime = ret['day']
        monthTime = ret['month']
        yearTime = ret['year']
        sqls = []
        sqls.append('INSERT INTO payment_record (rcd_id, user_id, acnt_id, timeme, amount, cat1_id, cat2_id, describebe, showable) VALUES (NULL, %s, %s, %s, %s, %s, %s, \'%s\', 1)'%(user_id, acnt_id, timeme, amount, cat1_id, cat2_id, description))
        sqls.append('UPDATE account_balance SET balance = balance - %s WHERE acnt_id = %s'%(amount, acnt_id))
        sqls.append('INSERT OR IGNORE INTO daily_stat_record(timeme, crc_id, income, payment) VALUES (%s, %s, 0, 0)'%(dayTime, crc_id))
        sqls.append('UPDATE daily_stat_record SET payment = payment + %s WHERE timeme = %s and crc_id = %s'%(amount, dayTime, crc_id))
        sqls.append('INSERT OR IGNORE INTO monthly_stat_record(timeme, crc_id, income, payment) VALUES (%s, %s, 0, 0)'%(monthTime, crc_id))
        sqls.append('UPDATE monthly_stat_record SET payment = payment + %s WHERE timeme = %s and crc_id = %s'%(amount, monthTime, crc_id))
        sqls.append('INSERT OR IGNORE INTO annually_stat_record(timeme, crc_id, income, payment) VALUES (%s, %s, 0, 0)'%(yearTime, crc_id))
        sqls.append('UPDATE annually_stat_record SET payment = payment + %s WHERE timeme = %s and crc_id = %s'%(amount, yearTime, crc_id))
        return self.__executeTransaction(sqls)

    def addTransferRecord(self, args):
        user_id = args['user_id']
        timeme = args['current_time']
        amount = args['amount']
        cat1_id = args['cat1_id']
        src_acnt_id = args['src_account_id']
        dst_acnt_id = args['dst_account_id']
        description = args['description']
        sqls = []
        sqls.append('INSERT INTO transfer_record (rcd_id, user_id, timeme, amount, cat1_id, src_acnt_id, dst_acnt_id, describebe, showable) VALUES (NULL, %s, %s, %s, %s, %s, %s, \'%s\', 1)'%(timeme, user_id, amount, cat1_id, src_acnt_id, dst_acnt_id, description))
        sqls.append('UPDATE account_balance SET balance = balance + %s WHERE acnt_id = %s'%(amount, dst_acnt_id))
        sqls.append('UPDATE account_balance SET balance = balance - %s WHERE acnt_id = %s'%(amount, src_acnt_id))
        return self.__executeTransaction(sqls)

    def deleteIncome(self, args):
        record_id = args['rcd_id']
        attrs = ['acnt_id', 'timeme', 'amount']
        ret = self.__selectData(attrs, 'income_record', 'rcd_id=' + str(record_id), False, '')
        if ret['errno'] != Error.SUCCESS:
            return ret
        ret = ret['data'][0]
        acnt_id = ret['acnt_id']
        timeme = ret['timeme']
        amount = ret['amount']
        ret = timestampToCurrentDayMonthYearTimestamp(timeme)
        dayTime = ret['day']
        monthTime = ret['month']
        yearTime = ret['year']
        sqls = []
        sqls.append('UPDATE account_balance SET balance = balance - ' +str(amount) + ' WHERE acnt_id = ' + str(acnt_id))
        sqls.append('UPDATE daily_stat_record SET income = income - ' +str(amount) + ' WHERE timeme = ' + str(dayTime))
        sqls.append('UPDATE monthly_stat_record SET income = income - ' +str(amount) + ' WHERE timeme = ' + str(monthTime))
        sqls.append('UPDATE annually_stat_record SET income = income - ' +str(amount) + ' WHERE timeme = ' + str(yearTime))
        sqls.append('DELETE FROM income_record WHERE rcd_id = ' + record_id)
        return self.__executeTransaction(sqls)

    def deletePayment(self, args):
        record_id = args['rcd_id']
        attrs = ['acnt_id', 'timeme', 'amount']
        ret = self.__selectData(attrs, 'payment_record', 'rcd_id=' + str(record_id), False, '')
        if ret['errno'] != Error.SUCCESS:
            return ret
        ret = ret['data'][0]
        acnt_id = ret['acnt_id']
        timeme = ret['timeme']
        amount = ret['amount']
        ret = timestampToCurrentDayMonthYearTimestamp(timeme)
        dayTime = ret['day']
        monthTime = ret['month']
        yearTime = ret['year']
        sqls = []
        sqls.append('UPDATE account_balance SET balance = balance + ' +str(amount) + ' WHERE acnt_id = ' + str(acnt_id))
        sqls.append('UPDATE daily_stat_record SET payment = payment - ' +str(amount) + ' WHERE timeme = ' + str(dayTime))
        sqls.append('UPDATE monthly_stat_record SET payment = payment - ' +str(amount) + ' WHERE timeme = ' + str(monthTime))
        sqls.append('UPDATE annually_stat_record SET payment = payment - ' +str(amount) + ' WHERE timeme = ' + str(yearTime))
        sqls.append('DELETE FROM payment_record WHERE rcd_id = ' + record_id)
        return self.__executeTransaction(sqls)

    def deleteTransfer(self, args):
        record_id = args['rcd_id']
        attrs = ['src_acnt_id', 'dst_acnt_id', 'amount']
        ret = self.__selectData(attrs, 'transfer_record', 'rcd_id=' + str(record_id), False, '')
        if ret['errno'] != Error.SUCCESS:
            return ret
        ret = ret['data'][0]
        src_acnt_id = ret['src_acnt_id']
        dst_acnt_id = ret['dst_acnt_id']
        amount = ret['amount']
        sqls = []
        sqls.append('UPDATE account_balance SET balance = balance + ' +str(amount) + ' WHERE acnt_id = ' + str(src_acnt_id))
        sqls.append('UPDATE account_balance SET balance = balance - ' +str(amount) + ' WHERE acnt_id = ' + str(dst_acnt_id))
        sqls.append('DELETE FROM transfer_record WHERE rcd_id = ' + record_id)
        return self.__executeTransaction(sqls)

    def editIncome(self, args):
        return makeReturn(Error.NOT_IMPLEMENT)

    def editPayment(self, args):
        return makeReturn(Error.NOT_IMPLEMENT)

    def editTransfer(self, args):
        return makeReturn(Error.NOT_IMPLEMENT)

    def getIncomeRecord(self, args):
        begin_time = args['begin_time']
        end_time = args['end_time']
        condition = 'WHERE timeme >= %s and timeme <= %s '%(begin_time, end_time)
        if 'account_id' in args:
            condition += 'and acnt_id = %s'%(args['account_id'])
        if 'cat1_id' in args:
            condition += 'and cat1_id = %s'%(args['cat1_id'])
        if 'user_id' in args:
            condition += 'and user_id = %s'%(args['user_id'])
        sqlStr = 'SELECT rcd_id, users.nameme, account.nameme, timeme, income_cat1.nameme, amount, describebe FROM \
            (SELECT * FROM income_record %s ORDER BY timeme DESC) AS rslt \
            LEFT JOIN account ON account.acnt_id = rslt.acnt_id \
            LEFT JOIN income_cat1 ON income_cat1.cat1_id = rslt.cat1_id \
            LEFT JOIN users ON users.user_id = rslt.user_id'%(condition)
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except:
            self.logger.warning('query failed. sql:' + sqlStr)
            return makeReturn(Error.DATABASE_ERROR)
        attrs = ['rcd_id', 'user_name', 'acnt_name', 'timeme', 'cat1_name', 'amount', 'describebe']
        ret = []
        for row in dbData:
            ret.append(dict(zip(attrs, row)))
        return makeReturn(Error.SUCCESS, ret)

    def getPaymentRecord(self, args):
        begin_time = args['begin_time']
        end_time = args['end_time']
        condition = 'WHERE timeme >= %s and timeme <= %s '%(begin_time, end_time)
        if 'account_id' in args:
            condition += 'and acnt_id = %s '%(args['account_id'])
        if 'cat1_id' in args:
            condition += 'and cat1_id = %s '%(args['cat1_id'])
        if 'cat2_id' in args:
            condition += 'and cat2_id = %s '%(args['cat2_id'])
        if 'user_id' in args:
            condition += 'and user_id = %s'%(args['user_id'])
        sqlStr = 'SELECT rcd_id, users.nameme, account.nameme, timeme, payment_cat1.nameme, payment_cat2.nameme, amount, describebe FROM \
            (SELECT * FROM payment_record %s ORDER BY timeme DESC) AS rslt \
            LEFT JOIN account ON account.acnt_id = rslt.acnt_id \
            LEFT JOIN payment_cat1 ON payment_cat1.cat1_id = rslt.cat1_id \
            LEFT JOIN payment_cat2 ON payment_cat2.cat2_id = rslt.cat2_id \
            LEFT JOIN users ON users.user_id = rslt.user_id'%(condition)
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except:
            self.logger.warning('query failed. sql:' + sqlStr)
            return makeReturn(Error.DATABASE_ERROR)
        attrs = ['rcd_id', 'user_name', 'acnt_name', 'timeme', 'cat1_name', 'cat2_name', 'amount', 'describebe']
        ret = []
        for row in dbData:
            ret.append(dict(zip(attrs, row)))
        return makeReturn(Error.SUCCESS, ret)

    def getTransferRecord(self, args):
        begin_time = args['begin_time']
        end_time = args['end_time']
        condition = 'WHERE timeme >= %s and timeme <= %s '%(begin_time, end_time)
        if 'src_account_id' in args:
            condition += 'and src_acnt_id = %s'%(args['src_account_id'])
        if 'dst_account_id' in args:
            condition += 'and dst_acnt_id = %s'%(args['dst_account_id'])
        if 'cat1_id' in args:
            condition += 'and cat1_id = %s'%(args['cat1_id'])
        if 'user_id' in args:
            condition += 'and user_id = %s'%(args['user_id'])
        sqlStr = 'SELECT rcd_id, users.nameme, timeme, acnt_1.nameme, acnt_2.nameme, transfer_cat1.nameme, amount, describebe FROM \
            (SELECT * FROM transfer_record %s ORDER BY timeme DESC) AS rslt \
            LEFT JOIN account AS acnt_1 ON acnt_1.acnt_id = rslt.src_acnt_id \
            LEFT JOIN account AS acnt_2 ON acnt_2.acnt_id = rslt.dst_acnt_id \
            LEFT JOIN transfer_cat1 ON transfer_cat1.cat1_id = rslt.cat1_id \
            LEFT JOIN users ON users.user_id = rslt.user_id'%(condition)
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except:
            self.logger.warning('query failed. sql:' + sqlStr)
            return makeReturn(Error.DATABASE_ERROR)
        attrs = ['rcd_id', 'user_name', 'timeme', 'src_acnt_name', 'dst_acnt_name', 'cat1_name', 'amount', 'describebe']
        ret = []
        for row in dbData:
            ret.append(dict(zip(attrs, row)))
        return makeReturn(Error.SUCCESS, ret)

    def getAllCurrency(self, args):
        attrs = ['crc_id', 'nameme', 'unit', 'characterter', 'showable']
        show_delete = args['show_delete']
        returnRaw = args['return_raw']
        condition = ''
        if show_delete == False:
            condition = 'showable = 1'
        return self.__selectData(attrs, 'currency', condition, returnRaw)

    def addCurrency(self, args):
        sqls = ['INSERT INTO currency(crc_id, nameme, unit, characterter, showable) VALUES (NULL,"%s","%s","%s",1)'%
                (args['nameme'], args['unit'], args['characterter'])]
        return self.__executeTransaction(sqls)

    def editCurrency(self, args):
        sqls = ['UPDATE currency SET nameme="%s", unit = "%s", characterter = "%s" WHERE crc_id = %s'%
            (args['nameme'], args['unit'], args['characterter'], args['crc_id'])]
        return self.__executeTransaction(sqls)
        
    def deleteCurrency(self, args):
        sqls = ['UPDATE currency SET showable = 0 WHERE crc_id = ' + args['crc_id']]
        return self.__executeTransaction(sqls)

    def getIncomeCat1Percentage(self, args):
        begin_time = args['begin_time']
        end_time = args['end_time']
        crc_id = args['currency_id']
        sqlStr = 'SELECT income_cat1.nameme, SUM(amount) FROM income_record \
            LEFT JOIN account on income_record.acnt_id = account.acnt_id \
            LEFT JOIN income_cat1 on income_record.cat1_id = income_cat1.cat1_id \
            WHERE account.crc_id = %s and income_record.timeme >= %s AND income_record.timeme <= %s \
            GROUP BY income_record.cat1_id'%(crc_id, begin_time, end_time)
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except:
            self.logger.warning('query failed. sql:' + sqlStr)
            return makeReturn(Error.DATABASE_ERROR)
        attrs = ['cat1_name', 'amount']
        ret = []
        for row in dbData:
            ret.append(dict(zip(attrs, row)))
        return makeReturn(Error.SUCCESS, ret)

    def getPaymentCat1Percentage(self, args):
        begin_time = args['begin_time']
        end_time = args['end_time']
        crc_id = args['currency_id']
        sqlStr = 'SELECT payment_cat1.nameme, SUM(amount) FROM payment_record \
            LEFT JOIN account on payment_record.acnt_id = account.acnt_id \
            LEFT JOIN payment_cat1 on payment_record.cat1_id = payment_cat1.cat1_id \
            WHERE account.crc_id = %s and payment_record.timeme >= %s AND payment_record.timeme <= %s \
            GROUP BY payment_record.cat1_id'%(crc_id, begin_time, end_time)
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except:
            self.logger.warning('query failed. sql:' + sqlStr)
            return makeReturn(Error.DATABASE_ERROR)
        attrs = ['cat1_name', 'amount']
        ret = []
        for row in dbData:
            ret.append(dict(zip(attrs, row)))
        return makeReturn(Error.SUCCESS, ret)

    def getIncomeAndPaymentTendency(self, args):
        begin_time = args['begin_time']
        end_time = args['end_time']
        crc_id = args['currency_id']
        time_interval = args['time_interval'] # 保證值是正確的
        table_name = ''
        if time_interval == 'day':
            table_name = 'daily_stat_record'
        elif time_interval == 'month':
            table_name = 'monthly_stat_record'
        else: # year
            table_name = 'annually_stat_record'
        sqlStr = 'SELECT timeme, income, payment from %s LEFT JOIN currency ON %s.crc_id = currency.crc_id \
            WHERE %s.crc_id = %s AND timeme >= %s AND timeme <= %s'%(table_name, table_name, table_name, crc_id, begin_time, end_time)
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except:
            self.logger.warning('query failed. sql:' + sqlStr)
            return makeReturn(Error.DATABASE_ERROR)
        attrs = ['timeme', 'income', 'payment']
        ret = []
        for row in dbData:
            ret.append(dict(zip(attrs, row)))
        return makeReturn(Error.SUCCESS, ret)

    def getIncomeAndPayment(self, args):
        timeme = args['time']
        crc_id = args['currency_id']
        time_interval = args['time_interval'] # 保證值是正確的
        table_name = ''
        if time_interval == 'day':
            table_name = 'daily_stat_record'
        elif time_interval == 'month':
            table_name = 'monthly_stat_record'
        else: # year
            table_name = 'annually_stat_record'
        sqlStr = 'SELECT timeme, income, payment from %s LEFT JOIN currency ON %s.crc_id = currency.crc_id \
            WHERE %s.crc_id = %s AND timeme = %s '%(table_name, table_name, table_name, crc_id, timeme)
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except:
            self.logger.warning('query failed. sql:' + sqlStr)
            return makeReturn(Error.DATABASE_ERROR)
        if len(dbData) == 0:
            dbData = [[timeme, 0, 0]]
        attrs = ['timeme', 'income', 'payment']
        return makeReturn(Error.SUCCESS, dict(zip(attrs, dbData[0])))

    def getDefaultCurrency(self, args):
        sqlStr = 'SELECT valuelue FROM miscellaneous WHERE nameme = "dft_crc_id"'
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except:
            self.logger.warning('query failed. sql:' + sqlStr)
            return makeReturn(Error.DATABASE_ERROR)
        dbData = dbData[0][0]
        return makeReturn(Error.SUCCESS, {'dft_crc_id':dbData})

    def getPaymentCostGroupedByCat1InTimeRange(self, args) :
        '''获取指定时间段内各个一级支出的总支出'''
        begin_time = args['begin_time']
        end_time = args['end_time']
        sqlStr = 'SELECT cat1_id, SUM(amount) as amount FROM payment_record WHERE timeme >= %s AND timeme <= %s GROUP BY cat1_id'%(begin_time, end_time)
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except:
            self.logger.warning('query failed. sql:' + sqlStr)
            return makeReturn(Error.DATABASE_ERROR)
        attrs = ['cat1_id', 'amount']
        ret = []
        for row in dbData:
            ret.append(dict(zip(attrs, row)))
        return makeReturn(Error.SUCCESS, ret)

    def getBudgetPlan(self, args):
        '''获取预算详情
        type: month/all，month代表获取某个月和全年预算，all代表全部月份和全年的预算
        month: type为month时，此参数表示指定的月份数'''
        attrs = ['cat1_nameme', 'cat1_id','mon1','mon2','mon3','mon4','mon5','mon6','mon7','mon8','mon9','mon10','mon11','mon12','all_year','descriptiontion']
        sqlStr = 'SELECT payment_cat1.nameme, payment_cat1.cat1_id,mon1,mon2,mon3,mon4,mon5,mon6,mon7,mon8,mon9,mon10,mon11,mon12,all_year,descriptiontion FROM budget_plan\
            LEFT JOIN payment_cat1 ON budget_plan.payment_cat1_id = payment_cat1.cat1_id'
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except:
            self.logger.warning('query failed. sql:' + sqlStr)
            return makeReturn(Error.DATABASE_ERROR)
        cat1_plan = []
        all_cat1s_plan = dict(zip(attrs, dbData[0]))
        for row in dbData[1:]:
            cat1_plan.append(dict(zip(attrs, row)))
        return makeReturn(Error.SUCCESS, {'cat1_plan':cat1_plan, 'all_cat1s_plan':all_cat1s_plan})

    def updateBudget(self, args):
        try:
            self.cursor.execute('BEGIN TRANSACTION')
            # 設置要更新的cat1的預算
            sqlStr = 'UPDATE budget_plan SET mon1=%s,mon2=%s,mon3=%s,mon4=%s,mon5=%s,mon6=%s,mon7=%s,mon8=%s,\
            mon9=%s,mon10=%s,mon11=%s,mon12=%s,all_year=%s,descriptiontion="%s" WHERE payment_cat1_id=%s'%\
            (args['mon1'],args['mon2'],args['mon3'],args['mon4'],args['mon5'],args['mon6'],args['mon7'],args['mon8'],args['mon9'],args['mon10'],args['mon11'],args['mon12'],\
            args['all_year'],args['descriptiontion'],args['cat1_id'])
            self.cursor.execute(sqlStr)
            # 計算所有cat1的預算總合
            sqlStr = 'SELECT SUM(mon1),SUM(mon2),SUM(mon3),SUM(mon4),SUM(mon5),SUM(mon6),\
            SUM(mon7),SUM(mon8),SUM(mon9),SUM(mon10),SUM(mon11),SUM(mon12),SUM(all_year)\
            FROM budget_plan WHERE payment_cat1_id != 1'
            dbData = self.cursor.execute(sqlStr).fetchall()
            dbData = dbData[0]
            # 更新總預算
            sqlStr = 'UPDATE budget_plan SET mon1=%s,mon2=%s,mon3=%s,mon4=%s,mon5=%s,mon6=%s,mon7=%s,mon8=%s,\
            mon9=%s,mon10=%s,mon11=%s,mon12=%s,all_year=%s,descriptiontion="%s" WHERE payment_cat1_id=%s'%\
            (dbData[0],dbData[1],dbData[2],dbData[3],dbData[4],dbData[5],dbData[6],\
            dbData[7],dbData[8],dbData[9],dbData[10],dbData[11],dbData[12],"All Cat1s Total",1)
            self.cursor.execute(sqlStr)
            self.connection.commit()
        except Exception as e:
            self.logger.warning('query failed.', e.args)
            return makeReturn(Error.DATABASE_ERROR)
        return makeReturn(Error.SUCCESS)
        
    def getAllCurrencyAndAccountsBalance(self, args):
        '''获取各个币种的各个账户的账户余额
        currency，account，account_balance三張表連接
        返回内容:[[crc_id, crc_nameme, crc_unit, crc_char, acnt_id, 
        acnt_typepe, acnt_nameme, acnt_showable, acnt_deleteable, balance]...]'''
        sqlStr = 'SELECT c.crc_id, c.nameme, c.unit, c.characterter, c.showable, a.acnt_id, a.typepe, a.nameme, a.showable, a.deleteable, ab.balance \
            FROM currency AS c LEFT JOIN account AS a ON c.crc_id = a.crc_id LEFT JOIN account_balance AS ab ON a.acnt_id = ab.acnt_id'
        if args['show_delete'] == False:
            sqlStr += ' WHERE c.showable = 1'
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except Exception as e:
            self.logger.warning('query failed.', e.args)
            return makeReturn(Error.DATABASE_ERROR)
        attrs = ['crc_id', 'crc_nameme', 'crc_unit', 'crc_char', 'crc_showable', 'acnt_id', 
        'acnt_typepe', 'acnt_nameme', 'acnt_showable', 'acnt_deleteable', 'acnt_balance']
        for idx in range(0, len(dbData)):
            dbData[idx] = dict(zip(attrs, dbData[idx]))
        return makeReturn(Error.SUCCESS, dbData)

    def getAllCurrencyAndAccountsInfo(self, args):
        '''獲取各個幣種的各個賬戶的信息
        currency，account兩張表連接'''
        sqlStr = 'SELECT c.crc_id, c.nameme, c.unit, c.characterter, a.acnt_id, a.typepe, a.nameme, a.showable, a.deleteable \
            FROM currency AS c LEFT JOIN account AS a ON c.crc_id = a.crc_id WHERE c.showable = 1 AND a.showable = 1'
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except Exception as e:
            self.logger.warning('query failed.', e.args)
            return makeReturn(Error.DATABASE_ERROR)
        attrs = ['crc_id', 'crc_nameme', 'crc_unit', 'crc_char', 'acnt_id', 
        'acnt_typepe', 'acnt_nameme', 'acnt_showable', 'acnt_deleteable']
        for idx in range(0, len(dbData)):
            dbData[idx] = dict(zip(attrs, dbData[idx]))
        return makeReturn(Error.SUCCESS, dbData)

    def getPaymentCategoryShortNames(self, args):
        pass

    def getAllUserInfo(self, args):
        '''获取所有用户的信息'''
        sqlStr = 'SELECT user_id, nameme FROM users'
        try:
            dbData = self.cursor.execute(sqlStr).fetchall()
        except Exception as e:
            self.logger.warning('query failed.', e.args)
            return makeReturn(Error.DATABASE_ERROR)
        attrs = ['user_id', 'nameme', 'md5']
        for idx in range(0, len(dbData)):
            dbData[idx] = dict(zip(attrs, dbData[idx]))
        return makeReturn(Error.SUCCESS, dbData)
        
